package com.ccxc.question.exam.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import java.util.UUID;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.ccxc.question.exam.dto.AreaDTO2;
import com.ccxc.question.exam.dto.ErrorDTO;
import com.ccxc.question.exam.dto.QuestionDTO;

@Component
public class QuestionDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public QuestionDTO add(QuestionDTO dto) {
        final String sql = "INSERT INTO  t_question (ID, CONTENT, ANSWER, KNOWLEDGE_AREA) VALUES (?, ?, ?, ?)";
        dto.setId(UUID.randomUUID().toString());
        jdbcTemplate.update(sql,
                dto.getId(), dto.getContent(), dto.getAnswer(), dto.getArea());
        return dto;
    }

    public List<QuestionDTO> findById2(List<ErrorDTO> list2) {
        final String sql = "SELECT ID, CONTENT, ANSWER,KNOWLEDGE_AREA  FROM  t_question WHERE ID = ?";
        List<QuestionDTO> list = jdbcTemplate.query(sql, new Object[]{list2}, new RowMapper<QuestionDTO>() {
            public QuestionDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                QuestionDTO question = new QuestionDTO();
                question.setId(rs.getString("ID"));
                question.setContent(rs.getString("CONTENT"));
                question.setAnswer(rs.getString("ANSWER"));
                question.setArea(rs.getString("KNOWLEDGE_AREA"));
                return question;
            }
        });

        if (null != list && list.size() > 0) {
            return list;
        }
        return null;
    }

    public QuestionDTO findById(String id) {
        final String sql = "SELECT ID, CONTENT, ANSWER,KNOWLEDGE_AREA  FROM  t_question WHERE ID = ?";
        List<QuestionDTO> list = jdbcTemplate.query(sql, new Object[]{id}, new RowMapper<QuestionDTO>() {
            public QuestionDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                QuestionDTO question = new QuestionDTO();
                question.setId(rs.getString("ID"));
                question.setContent(rs.getString("CONTENT"));
                question.setAnswer(rs.getString("ANSWER"));
                question.setArea(rs.getString("KNOWLEDGE_AREA"));
                return question;
            }
        });

        if (null != list && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public QuestionDTO update(QuestionDTO dto) {
        final String sql = "UPDATE  t_question SET CONTENT = ?, ANSWER = ?, KNOWLEDGE_AREA = ? WHERE ID = ?";
        jdbcTemplate.update(sql,
                new Object[]{dto.getContent(), dto.getAnswer(), dto.getArea(), dto.getId()},
                new int[]{Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR}
        );
        return dto;
    }

    public int delete(String id) {
        final String sql = "DELETE FROM  t_question WHERE ID = ?";
        return jdbcTemplate.update(sql,
                new Object[]{id},
                new int[]{Types.VARCHAR}
        );
    }

    public QuestionDTO look() {
        final String sql = "SELECT ID, CONTENT, ANSWER,KNOWLEDGE_AREA  FROM `t_question`  ORDER BY RAND() LIMIT 1;";
        List<QuestionDTO> list = jdbcTemplate.query(sql, new Object[]{}, new RowMapper<QuestionDTO>() {
            public QuestionDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                QuestionDTO question = new QuestionDTO();
                question.setId(rs.getString("ID"));
                question.setContent(rs.getString("CONTENT"));
                question.setAnswer(rs.getString("ANSWER"));
                question.setArea(rs.getString("KNOWLEDGE_AREA"));
                return question;
            }
        });
        if (null != list && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public QuestionDTO lookarea(String area1, String area2, String area3) {
        final String sql = "SELECT ID, CONTENT, ANSWER,KNOWLEDGE_AREA  FROM `t_question` WHERE KNOWLEDGE_AREA = ? OR  KNOWLEDGE_AREA = ? OR KNOWLEDGE_AREA = ? ORDER BY RAND() LIMIT 1";
        List<QuestionDTO> list = jdbcTemplate.query(sql, new Object[]{area1, area2, area3}, new RowMapper<QuestionDTO>() {
            public QuestionDTO mapRow(ResultSet rs, int rowNum) throws SQLException {
                QuestionDTO question = new QuestionDTO();
                question.setId(rs.getString("ID"));
                question.setContent(rs.getString("CONTENT"));
                question.setAnswer(rs.getString("ANSWER"));
                question.setArea(rs.getString("KNOWLEDGE_AREA"));
                return question;
            }
        });
        if (null != list && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public List<AreaDTO2> area() {
        final String sql = "SELECT KNOWLEDGE_AREA FROM t_question GROUP BY KNOWLEDGE_AREA";
        List<AreaDTO2> list = jdbcTemplate.query(sql, new Object[]{}, new RowMapper<AreaDTO2>() {
            public AreaDTO2 mapRow(ResultSet rs, int rowNum) throws SQLException {
                AreaDTO2 areaDTO = new AreaDTO2();
                areaDTO.setArea(rs.getString("KNOWLEDGE_AREA"));
                return areaDTO;
            }
        });
        if (null != list && list.size() > 0) {
            return list;
        }
        return null;
    }
}
